package org.vincen.helloworld.poi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.WorkbookUtil;

@SuppressWarnings("deprecation")
public class NewWorkBook {

	public static void main(String[] args) {
		Workbook wb = new HSSFWorkbook();
		CreationHelper helper = wb.getCreationHelper();
		FileOutputStream fos = null;
		Sheet sheet1 = wb.createSheet("new sheet");
		//this utility replaces invalid characters with a space (' ')
		String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]");
		@SuppressWarnings("unused")
		Sheet sheet2 = wb.createSheet(safeName);
		
		//第一行
		// Create a row and put some cells in it. Rows are 0 based.
	    Row row = sheet1.createRow((short)0);
	    
	    // Create a cell and put a value in it.
	    Cell cell = row.createCell(0);
	    cell.setCellValue(1);
	    
	    // Or do it on one line.
	    row.createCell(1).setCellValue(1.2);
	    
	    //使用createRichTextString创建文本
	    row.createCell(2).setCellValue(helper.createRichTextString("This is a string"));
	    
	    row.createCell(3).setCellValue(true);
	    
	    //不使用creationHelper创建文本
	    row.createCell(4).setCellValue("this is normal string");
	    
	    // Create a cell and put a date value in it. This cell is not styled as a date.
	    row.createCell(5).setCellValue(new Date());
	    
	    // we style the second cell as a date (and time). It is important to
	    // create a new cell style from the workbook otherwise you can end up
	    // modifying the built in style and effecting not only this cell but other cells.
	    CellStyle cellstyle = wb.createCellStyle();
	    cellstyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
	    cell = row.createCell(6);
	    cell.setCellValue(new Date());
	    cell.setCellStyle(cellstyle);
	    
	    //you can also set date as java.util.Calendar
	    cell = row.createCell(7);
	    cell.setCellValue(Calendar.getInstance());
	    cell.setCellStyle(cellstyle);
	    
	    //第二行
	    Row row1 = sheet1.createRow(1);
	    row1.createCell(0).setCellValue(1.1);
	    row1.createCell(1).setCellValue(new Date());
	    row1.createCell(2).setCellValue(Calendar.getInstance());
	    row1.createCell(3).setCellValue("a string");
	    row1.createCell(4).setCellValue(true);
	    row1.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);
	    
	    //第三行  演示对齐
	    Row row2 = sheet1.createRow((short) 2);
        row2.setHeightInPoints(30);

        createCell(wb, row2, (short) 0, CellStyle.ALIGN_CENTER, 		  CellStyle.VERTICAL_BOTTOM);
        createCell(wb, row2, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
        createCell(wb, row2, (short) 2, CellStyle.ALIGN_FILL,			  CellStyle.VERTICAL_CENTER);
        createCell(wb, row2, (short) 3, CellStyle.ALIGN_GENERAL,		  CellStyle.VERTICAL_CENTER);
        createCell(wb, row2, (short) 4, CellStyle.ALIGN_JUSTIFY,		  CellStyle.VERTICAL_JUSTIFY);
        createCell(wb, row2, (short) 5, CellStyle.ALIGN_LEFT,			  CellStyle.VERTICAL_TOP);
        createCell(wb, row2, (short) 6, CellStyle.ALIGN_RIGHT,			  CellStyle.VERTICAL_TOP);

        //第四行  处理边界，边界宽度、边界颜色
        Row row3 = sheet1.createRow(3);
        // Create a cell and put a value in it.
        Cell cell1 = row3.createCell(1);
        cell1.setCellValue(4);
        // Style the cell with borders all around.
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.RED.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.RED.getIndex());
        style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.RED.getIndex());
        cell1.setCellStyle(style);
        
        //循环行、列
        Sheet sheet3 = wb.getSheetAt(0);
        //直接打印成字符串
        for(Row r : sheet3){
        	for(Cell c : r){
        		System.out.print(c.toString());
        		System.out.print(" ");
        	}
        	System.out.println();
        }
        //返回原始类型
        for(Row r : sheet3){
        	for(Cell c : r){
        		CellReference ref = new CellReference(r.getRowNum(), c.getColumnIndex());
        		System.out.print(ref.formatAsString());
        		System.out.print(" - ");
        		switch (c.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					System.out.println(c.getRichStringCellValue().getString());
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if(DateUtil.isCellDateFormatted(c)){
						System.out.println(c.getDateCellValue());
					}else {
						System.out.println(c.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					System.out.println(c.getBooleanCellValue());
					break;
				case Cell.CELL_TYPE_FORMULA:
					System.out.println(c.getCellFormula());
					break;
				default:
					System.out.println();
				}
        	}
        }
        
        //抓去excel中的表格内容
        InputStream is = null;
        HSSFWorkbook wb1 = null;
		try {
			is = new FileInputStream("workbooks.xls");
			wb1 = new HSSFWorkbook(new POIFSFileSystem(is));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
        ExcelExtractor extractor = new ExcelExtractor(wb1);

        extractor.setFormulasNotResults(true);
        extractor.setIncludeSheetNames(false);
        String text = extractor.getText();
        System.out.println(text);
        
        //第五行
        //merging cells
        Row row4 = sheet1.createRow((short) 4);
        Cell cell2 = row4.createCell((short) 1);
        cell2.setCellValue("This is a test of merging");

        sheet1.addMergedRegion(new CellRangeAddress(
                4, //first row (0-based)
                4, //last row  (0-based)
                1, //first column (0-based)
                2  //last column  (0-based)
        ));
        
        //操作填充，颜色，字体，见如下地址
        //http://poi.apache.org/spreadsheet/quick-guide.html
        
        //
        try {
			fos = new FileOutputStream("workbooks.xls");
			wb.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     */
    private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
        Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }
	
}
